<%@page language="java" contentType="text/javascript; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page import='java.sql.*' %>
<%@ page import="java.util.Calendar"%>
<%@ page import="java.util.Date"%>
<%@ page import="java.text.DecimalFormat"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@page import="java.util.ArrayList"%>
<%@page import="net.sf.json.JSONObject"%>
<%@page import="net.sf.json.JSONSerializer"%>
<%@page import="net.sf.json.JSONArray"%>

<%String callback = request.getParameter("callback");%>
<%=callback %> (<%
	
	String id = request.getParameter("id");	
	String getLat = request.getParameter("userlat");
	String getLng = request.getParameter("userlng");
	int getTheme = Integer.parseInt(request.getParameter("usertheme"));
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	PreparedStatement tingpstmt = null;
    try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost/itscreater", "itscreater", "campus123");
		
		String select = "select * from TingAttendee where ID = ?;";
		pstmt = conn.prepareStatement(select);
		pstmt.setString(1, id);
		ResultSet rs = pstmt.executeQuery();
		
		JSONObject jo = new JSONObject();
		JSONArray ja = new JSONArray();
		
		while(rs.next()){
			String tingnum = rs.getString("TingNumber");
			
			//String tingselect = "select * from TingCopy where Number = ?;";
			String tingselect;
			
			switch (getTheme) {
			case 1:
				tingselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
						" CASE TingTheme WHEN 1 THEN 20 WHEN 2 THEN 17 WHEN 3 THEN 8 WHEN 4 THEN 3 WHEN 5 THEN 15 WHEN 6 THEN 3 ELSE 20 END AS theme"+
						" from Ting where Number = ?) a ORDER BY totalvalue DESC;";
				tingpstmt = conn.prepareStatement(tingselect);
				tingpstmt.setString(1, getLat);
				tingpstmt.setString(2, getLng);
				tingpstmt.setString(3, getLat);
				tingpstmt.setString(4, getLat);
				tingpstmt.setString(5, getLng);
				tingpstmt.setString(6, getLat);
				tingpstmt.setString(7, tingnum);
				break;
			case 2:
				tingselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
						" CASE TingTheme WHEN 1 THEN 17 WHEN 2 THEN 20 WHEN 3 THEN 15 WHEN 4 THEN 3 WHEN 5 THEN 8 WHEN 6 THEN 15 ELSE 20 END AS theme"+
						" from Ting where Number = ?) a ORDER BY totalvalue DESC;";
				tingpstmt = conn.prepareStatement(tingselect);
				tingpstmt.setString(1, getLat);
				tingpstmt.setString(2, getLng);
				tingpstmt.setString(3, getLat);
				tingpstmt.setString(4, getLat);
				tingpstmt.setString(5, getLng);
				tingpstmt.setString(6, getLat);
				tingpstmt.setString(7, tingnum);
				break;
			case 3:
				tingselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
						" CASE TingTheme WHEN 1 THEN 8 WHEN 2 THEN 15 WHEN 3 THEN 20 WHEN 4 THEN 3 WHEN 5 THEN 5 WHEN 6 THEN 12 ELSE 20 END AS theme"+
						" from Ting where Number = ?) a ORDER BY totalvalue DESC;";
				tingpstmt = conn.prepareStatement(tingselect);
				tingpstmt.setString(1, getLat);
				tingpstmt.setString(2, getLng);
				tingpstmt.setString(3, getLat);
				tingpstmt.setString(4, getLat);
				tingpstmt.setString(5, getLng);
				tingpstmt.setString(6, getLat);
				tingpstmt.setString(7, tingnum);
				break;
			case 4:
				tingselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
						" CASE TingTheme WHEN 1 THEN 3 WHEN 2 THEN 3 WHEN 3 THEN 3 WHEN 4 THEN 20 WHEN 5 THEN 3 WHEN 6 THEN 3 ELSE 20 END AS theme"+
						" from Ting where Number = ?) a ORDER BY totalvalue DESC;";
				tingpstmt = conn.prepareStatement(tingselect);
				tingpstmt.setString(1, getLat);
				tingpstmt.setString(2, getLng);
				tingpstmt.setString(3, getLat);
				tingpstmt.setString(4, getLat);
				tingpstmt.setString(5, getLng);
				tingpstmt.setString(6, getLat);
				tingpstmt.setString(7, tingnum);
				break;
			case 5:
				tingselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
						" CASE TingTheme WHEN 1 THEN 15 WHEN 2 THEN 8 WHEN 3 THEN 5 WHEN 4 THEN 3 WHEN 5 THEN 20 WHEN 6 THEN 5 ELSE 20 END AS theme"+
						" from Ting where Number = ?) a ORDER BY totalvalue DESC;";
				tingpstmt = conn.prepareStatement(tingselect);
				tingpstmt.setString(1, getLat);
				tingpstmt.setString(2, getLng);
				tingpstmt.setString(3, getLat);
				tingpstmt.setString(4, getLat);
				tingpstmt.setString(5, getLng);
				tingpstmt.setString(6, getLat);
				tingpstmt.setString(7, tingnum);
				break;
			case 6:
				tingselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
						" CASE TingTheme WHEN 1 THEN 8 WHEN 2 THEN 15 WHEN 3 THEN 12 WHEN 4 THEN 3 WHEN 5 THEN 5 WHEN 6 THEN 20 ELSE 20 END AS theme"+
						" from Ting where Number = ?) a ORDER BY totalvalue DESC;";
				tingpstmt = conn.prepareStatement(tingselect);
				tingpstmt.setString(1, getLat);
				tingpstmt.setString(2, getLng);
				tingpstmt.setString(3, getLat);
				tingpstmt.setString(4, getLat);
				tingpstmt.setString(5, getLng);
				tingpstmt.setString(6, getLat);
				tingpstmt.setString(7, tingnum);
				break;			
			case 7:
				tingselect = "select *, CASE WHEN timescore<0 THEN 0 ELSE timescore END+CASE WHEN distancescore<0 THEN 0 ELSE distancescore END+theme as totalvalue"+
						" from (select Number, ID, Host, Title, TingTheme, Lat, Lng, Content, Date," + 
						" ( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ) AS distance,"+
						" TIMESTAMPDIFF(MINUTE, Date, NOW()) AS time,"+
						" 30-30/POW(360, 2)*POW(TIMESTAMPDIFF(MINUTE, Date, NOW()), 2) AS timescore,"+
						" 50-50/400*POW(( 6371 * acos( cos( radians(?) ) * cos( radians( Lat ) ) * cos( radians( Lng ) - radians(?) ) + sin( radians(?) ) * sin( radians( Lat ) ) ) ), 2) AS distancescore,"+
						" 20 AS theme"+
						" from Ting where Number = ?) a ORDER BY totalvalue DESC;";
				tingpstmt = conn.prepareStatement(tingselect);
				tingpstmt.setString(1, getLat);
				tingpstmt.setString(2, getLng);
				tingpstmt.setString(3, getLat);
				tingpstmt.setString(4, getLat);
				tingpstmt.setString(5, getLng);
				tingpstmt.setString(6, getLat);
				tingpstmt.setString(7, tingnum);
				break;
			}

			ResultSet tingrs = tingpstmt.executeQuery();
			
			while(tingrs.next()) {
				String num = tingrs.getString("Number");
				String hostid = tingrs.getString("ID");
				String host = tingrs.getString("Host");
				String title = tingrs.getString("Title");
				String theme = tingrs.getString("TingTheme");
				String lat = tingrs.getString("Lat");
				String lng = tingrs.getString("Lng");
				String content = tingrs.getString("Content");
				if (title != null) {
					title = title.replace("\n","<br>");
					title = title.replace("\u0020","&nbsp;");
				}
				if (content != null) {
					content = content.replace("\n","<br>");
					content = content.replace("\u0020","&nbsp;");
				}
				String date = tingrs.getString("Date").substring(0,16);
				String getdistance = tingrs.getString("distance");
				String gettime = tingrs.getString("time");
				String gettheme = tingrs.getString("theme");
				String dot = "\\.";
				String gettotalvalue = tingrs.getString("totalvalue");
				String[] totalArray = gettotalvalue.split(dot);
				String finaltotal = totalArray[0];
				double totalvalue = Double.parseDouble(gettotalvalue);
				
				if (totalvalue >= 0 && totalvalue < 25){
					String wifi = "0";
					jo.put("Wifi", wifi);
				} else if (totalvalue >= 25 && totalvalue <50){
					String wifi = "1";
					jo.put("Wifi", wifi);
				} else if (totalvalue >= 50 && totalvalue <75){
					String wifi = "2";
					jo.put("Wifi", wifi);
				} else if (totalvalue >= 75 && totalvalue <= 100){
					String wifi = "3";
					jo.put("Wifi", wifi);
				}
				
				
				jo.put("NUMBER", num);
				jo.put("ID", hostid);
				jo.put("HOST", host);
				jo.put("TITLE", title);
				jo.put("THEME", theme);
				jo.put("LAT", lat);
				jo.put("LNG", lng);
				jo.put("CONTENT", content);
				jo.put("DATE", date);
				jo.put("Distance", getdistance);
				jo.put("Time", gettime);
				jo.put("ThemeScore", gettheme);
				jo.put("Total", finaltotal);
				jo.put("success", "true");
				ja.add(jo);
			}
			
		}
		
		out.print(ja);
    }
    
	finally {
		try { 
            pstmt.close();
        } 
        catch (Exception ignored) {
        }
		try { 
            tingpstmt.close();
        } 
        catch (Exception ignored) {
        }
        try { 
            conn.close();
        } 
        catch (Exception ignored) {
        }
    }
%>);